package com.zlt.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.zlt.annotation.Column;
import com.zlt.annotation.Id;
import com.zlt.annotation.ManyToOne;
import com.zlt.annotation.Table;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

/**
 * SQL 工具类
 *
 * @author H
 * @date 2022/07/15
 */
public class SqlUtil {
    
    private static DataSource dataSource;
    
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
    
    static {
        // 加载配置文件
        InputStream is = SqlUtil.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        try {
            // 读取配置文件中的内容
            properties.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        
        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    
    /**
     * 从 ThreadLocal 中获取当前的数据库连接对象，没有则新建一个，并放入 ThreadLocal 中
     *
     * @return 数据库连接对象
     */
    public static Connection getCurrentConnection() {
        Connection connection = threadLocal.get();
        if (connection == null) {
            connection = getConnection();
            threadLocal.set(connection);
        }
        return connection;
    }
    
    /**
     * 获取数据库连接
     *
     * @return 数据库连接对象
     */
    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    
    /**
     * 提交事务
     */
    public static void commit() {
        Connection currentConnection = getCurrentConnection();
        
        if (currentConnection != null) {
            try {
                if (!currentConnection.isClosed()) {
                    currentConnection.commit();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    
    /**
     * 回滚事务
     */
    public static void rollback() {
        Connection currentConnection = getCurrentConnection();
    
        if (currentConnection != null) {
            try {
                if (!currentConnection.isClosed()) {
                    currentConnection.rollback();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    
    /**
     * 开启事务（手动管理）
     */
    public static void beginTransaction() {
        Connection currentConnection = getCurrentConnection();
    
        if (currentConnection != null) {
            try {
                if (!currentConnection.isClosed()) {
                    currentConnection.setAutoCommit(false);
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    
    /**
     * 释放资源，关闭连接
     *
     * @param rs   结果集对象
     * @param pst  预处理对象
     * @param conn 数据库连接对象
     */
    public static void close(ResultSet rs, PreparedStatement pst, Connection conn) {
        threadLocal.remove();
        
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
        
        if (pst != null) {
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
        
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
    }
    
    /**
     * 增删改操作最终需要执行的操作
     *
     * @param sql    增删改 SQL 语句
     * @param params 参数
     * @return 影响行数；-1 连接异常
     */
    public static int update(String sql, Object... params) {
        Connection conn = getCurrentConnection();
        int result = -1;
        
        if (conn != null) {
            PreparedStatement pst;
            try {
                pst = conn.prepareStatement(sql);
                if (params != null) {
                    for (int i = 1; i <= params.length; i++) {
                        pst.setObject(i, params[i - 1]);
                    }
                }
                result = pst.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
        
        return result;
    }
    
    /**
     * 执行增加：
     * 1. 需要将对象反射拼接成一个 SQL 语句 => INSERT INTO 表名 ( 字段1, 字段2, ... ) VALUES ( ?, ?, ... )
     * 2. 获取属性中的值，作为参数
     *
     * @param obj 封装了待添加数据的对象
     * @return 影响行数；-1 操作失败
     */
    public static int insert(Object obj) {
        Class<?> clazz = obj.getClass();
        
        if (clazz != null) {
            if (clazz.isAnnotationPresent(Table.class)) {
                StringBuilder beforeSql = new StringBuilder();
                StringBuilder afterSql = new StringBuilder();
                List<Object> params = new ArrayList<>();
                
                beforeSql.append("INSERT INTO ");
                afterSql.append(" ) VALUES ( ");
                
                Table table = clazz.getAnnotation(Table.class);
                String tableName = table.value();
                if (!StringUtil.isNotNull(tableName)) {
                    tableName = clazz.getSimpleName();
                }
                beforeSql.append(tableName);
                beforeSql.append(" ( ");
                
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true);
                    
                    Object value;
                    try {
                        value = field.get(obj);
                    } catch (IllegalAccessException e) {
                        throw new RuntimeException(e);
                    }
                    
                    if (value != null) {
                        if (field.isAnnotationPresent(Id.class)) {
                            Id id = field.getAnnotation(Id.class);
                            String idName = id.value();
                            if (!StringUtil.isNotNull(idName)) {
                                idName = field.getName();
                            }
                            beforeSql.append(idName);
                            beforeSql.append(", ");
                            afterSql.append("?, ");
                            params.add(value);
                        }
                        
                        if (field.isAnnotationPresent(Column.class)) {
                            Column column = field.getAnnotation(Column.class);
                            String fieldName = column.value();
                            if (!StringUtil.isNotNull(fieldName)) {
                                fieldName = field.getName();
                            }
                            beforeSql.append(fieldName);
                            beforeSql.append(", ");
                            afterSql.append("?, ");
                            params.add(value);
                        }
                        
                        if (field.isAnnotationPresent(ManyToOne.class)) {
                            ManyToOne manyToOne = field.getAnnotation(ManyToOne.class);
                            String fieldName = manyToOne.value();
                            if (!StringUtil.isNotNull(fieldName)) {
                                fieldName = getPrimaryKeyName(field.getType());
                            }
                            beforeSql.append(fieldName);
                            beforeSql.append(", ");
                            afterSql.append("?, ");
                            params.add(getPrimaryKeyValue(value));
                        }
                    }
                }
                
                // 去除多余 ", "
                String beforeString = beforeSql.substring(0, beforeSql.length() - 2);
                String afterString = afterSql.substring(0, afterSql.length() - 2) + " )";
                
                String sql = beforeString + afterString;
                return update(sql, params.toArray());
            }
        }
        
        return -1;
    }
    
    
    /**
     * 通过类型，获取获取对象中对应主键的名称
     *
     * @param clazz 待获取主键名称的类型
     * @return 主键名称
     */
    private static String getPrimaryKeyName(Class<?> clazz) {
        if (clazz != null) {
            if (clazz.isAnnotationPresent(Table.class)) {
                String idName = null;
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true);
                    if (field.isAnnotationPresent(Id.class)) {
                        Id id = field.getAnnotation(Id.class);
                        idName = id.value();
                        if (!StringUtil.isNotNull(idName)) {
                            idName = field.getName();
                        }
                        break;
                    }
                }
                return idName;
            }
        }
        return null;
    }
    
    /**
     * 通过对象，获取对象中对应主键的值
     *
     * @param obj 待获取主键值的对象
     * @return 主键值
     */
    private static Object getPrimaryKeyValue(Object obj) {
        Class<?> clazz = obj.getClass();
        Object value = null;
        
        if (clazz != null) {
            if (clazz.isAnnotationPresent(Table.class)) {
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true);
                    if (field.isAnnotationPresent(Id.class)) {
                        try {
                            value = field.get(obj);
                        } catch (IllegalAccessException e) {
                            throw new RuntimeException(e);
                        }
                        break;
                    }
                }
            }
        }
        
        return value;
    }
    
    
    /**
     * 执行修改:
     * 1. 需要将对象反射拼接成一个 SQL 语句 => UPDATE 表名 SET 字段1 = ? , 字段2 = ?  WHERE 主键 = ?
     * 2. 获取属性中的值，作为参数
     *
     * @param obj 封装了待修改数据的对象
     * @return 影响行数；-1 操作失败
     */
    public static int update(Object obj) {
        Class<?> clazz = obj.getClass();
        
        if (clazz != null) {
            if (clazz.isAnnotationPresent(Table.class)) {
                StringBuilder beforeSql = new StringBuilder();
                StringBuilder afterSql = new StringBuilder();
                List<Object> params = new ArrayList<>();
                Object idValue = null;
                
                beforeSql.append("UPDATE ");
                afterSql.append(" WHERE ");
                
                Table table = clazz.getAnnotation(Table.class);
                String tableName = table.value();
                if (!StringUtil.isNotNull(tableName)) {
                    tableName = clazz.getSimpleName();
                }
                beforeSql.append(tableName);
                beforeSql.append(" SET ");
                
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true);
                    Object value;
                    try {
                        value = field.get(obj);
                    } catch (IllegalAccessException e) {
                        throw new RuntimeException(e);
                    }
                    
                    if (value != null) {
                        if (field.isAnnotationPresent(Id.class)) {
                            Id id = field.getAnnotation(Id.class);
                            String idName = id.value();
                            if (!StringUtil.isNotNull(idName)) {
                                idName = field.getName();
                            }
                            afterSql.append(idName);
                            afterSql.append(" = ?");
                            idValue = value;
                        }
                        
                        if (field.isAnnotationPresent(Column.class)) {
                            Column column = field.getAnnotation(Column.class);
                            String fieldName = column.value();
                            if (!StringUtil.isNotNull(fieldName)) {
                                fieldName = field.getName();
                            }
                            beforeSql.append(fieldName);
                            beforeSql.append(" = ?, ");
                            params.add(value);
                        }
                    }
                }
                
                String sql = beforeSql.substring(0, beforeSql.length() - 2) + afterSql.toString();
                params.add(idValue);
                
                return update(sql, params.toArray());
            }
        }
        
        return -1;
    }
    
    /**
     * 执行删除:
     * 1. 需要将对象反射拼接成一个 SQL 语句 => DELETE FROM 表名 WHERE 主键 = ?
     * 2. 获取属性中的值，作为参数
     *
     * @param obj 封装了待删除数据的对象
     * @return 影响行数：-1 操作失败
     */
    public static int delete(Object obj) {
        Class<?> clazz = obj.getClass();
        
        if (clazz != null) {
            if (clazz.isAnnotationPresent(Table.class)) {
                StringBuilder beforeSql = new StringBuilder();
                StringBuilder afterSql = new StringBuilder();
                Object idValue = null;
                
                beforeSql.append("DELETE FROM ");
                afterSql.append(" WHERE ");
                
                Table table = clazz.getAnnotation(Table.class);
                String tableName = table.value();
                if (!StringUtil.isNotNull(tableName)) {
                    tableName = clazz.getSimpleName();
                }
                beforeSql.append(tableName);
                
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true);
                    Object value;
                    try {
                        value = field.get(obj);
                    } catch (IllegalAccessException e) {
                        throw new RuntimeException(e);
                    }
                    
                    if (value != null) {
                        if (field.isAnnotationPresent(Id.class)) {
                            Id id = field.getAnnotation(Id.class);
                            String idName = id.value();
                            if (!StringUtil.isNotNull(idName)) {
                                idName = field.getName();
                            }
                            afterSql.append(idName);
                            afterSql.append(" = ?");
                            idValue = value;
                        }
                    }
                }
                String sql = beforeSql.toString() + afterSql.toString();
                return update(sql, idValue);
            }
        }
        
        return -1;
    }
    
    /**
     * 通用查询，查询结果不用封装成对象
     *
     * @param sql    查询的 SQL 语句
     * @param params 参数
     * @return 查询结果；null 连接异常
     */
    public static List<Map<String, Object>> select(String sql, Object... params) {
        Connection conn = getCurrentConnection();
        
        if (conn != null) {
            List<Map<String, Object>> list = new ArrayList<>();
            PreparedStatement pst;
            ResultSet rs;
            try {
                pst = conn.prepareStatement(sql);
                if (params != null) {
                    for (int i = 0; i < params.length; i++) {
                        pst.setObject(i + 1, params[i]);
                    }
                }
                rs = pst.executeQuery();
                while (rs.next()) {
                    Map<String, Object> map = new HashMap<>();
                    ResultSetMetaData metaData = rs.getMetaData();
                    int columnCount = metaData.getColumnCount();
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnName(i);
                        map.put(columnName, rs.getObject(columnName));
                    }
                    list.add(map);
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            return list;
        }
        
        return null;
    }
    
    
    /**
     * 执行查询（单条数据）
     *
     * @param clazz  封装查询结果的实体类的类型
     * @param sql    查询单条数据的 SQL 语句
     * @param params 参数
     * @param <T>    返回值类型
     * @return 查询结果对象；null 操作失败
     */
    public static <T> T selectOne(Class<T> clazz, String sql, Object... params) {
        List<T> list = select(clazz, sql, params);
        if (list != null && !list.isEmpty()) {
            return list.get(0);
        }
        return null;
    }
    
    
    /**
     * 单表查询
     * 多表查询 多对一的情况查询  要求类和其中对象属性中的属性不能有重复
     *
     * @param clazz  封装查询结果的实体类的类型
     * @param sql    查询的 SQL 语句
     * @param params 参数
     * @param <T>    返回值类型
     * @return 查询结果对象；null 操作失败
     */
    public static <T> List<T> select(Class<T> clazz, String sql, Object... params) {
        Connection conn = getCurrentConnection();
        
        if (conn != null) {
            PreparedStatement pst;
            ResultSet rs;
            List<T> list = new ArrayList<>();
            try {
                pst = conn.prepareStatement(sql);
                if (params != null) {
                    for (int i = 0; i < params.length; i++) {
                        pst.setObject(i + 1, params[i]);
                    }
                }
                rs = pst.executeQuery();
                while (rs.next()) {
                    ResultSetMetaData metaData = rs.getMetaData();
                    T obj = clazz.newInstance();
                    
                    int columnCount = metaData.getColumnCount();
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnName(i);
                        setValue(obj, columnName, rs);
                    }
                    list.add(obj);
                }
            } catch (SQLException | IllegalAccessException | InstantiationException e) {
                throw new RuntimeException(e);
            }
            return list;
        }
        
        return null;
    }
    
    /**
     * 将查询出来的内容放入对象
     *
     * @param obj        封装查询数据的对象
     * @param columnName 列名
     * @param rs         结果集对象
     */
    private static void setValue(Object obj, String columnName, ResultSet rs) {
        Class<?> clazz = obj.getClass();
        
        if (clazz != null) {
            Field[] fields = clazz.getDeclaredFields();
            Field field = getField(clazz, columnName);
            
            if (field != null) {
                Class<?> type = field.getType();
                field.setAccessible(true);
                try {
                    if (type == int.class || type == Integer.class) {
                        field.set(obj, rs.getInt(columnName));
                    } else if (type == String.class) {
                        field.set(obj, rs.getString(columnName));
                    } else if (type == double.class || type == Double.class) {
                        field.set(obj, rs.getDouble(columnName));
                    } else if (type == long.class || type == Long.class) {
                        field.set(obj, rs.getLong(columnName));
                    }
                } catch (IllegalAccessException | SQLException e) {
                    throw new RuntimeException(e);
                }
            } else {
                for (Field tempField : fields) {
                    tempField.setAccessible(true);
                    if (tempField.isAnnotationPresent(ManyToOne.class)) {
                        Field field1 = getField(tempField.getType(), columnName);
                        if (field1 != null) {
                            try {
                                Object o = tempField.get(obj);
                                if (o == null) {
                                    o = tempField.getType().newInstance();
                                    tempField.set(obj, o);
                                }
                                setValue(o, columnName, rs);
                            } catch (IllegalAccessException | InstantiationException e) {
                                throw new RuntimeException(e);
                            }
                            break;
                        }
                    }
                }
            }
        }
    }
    
    /**
     * 获取类中指定名称的属性
     *
     * @param clazz      待获取字段的类
     * @param columnName 列名
     * @return 属性
     */
    private static Field getField(Class<?> clazz, String columnName) {
        if (clazz != null) {
            Field[] fields = clazz.getDeclaredFields();
            Field field = null;
            for (Field tempField : fields) {
                if (tempField.isAnnotationPresent(Id.class)) {
                    Id id = tempField.getAnnotation(Id.class);
                    if (("".equals(id.value()) && tempField.getName().equals(columnName)) || columnName.equals(id.value())) {
                        field = tempField;
                    }
                } else if (tempField.isAnnotationPresent(Column.class)) {
                    Column column = tempField.getAnnotation(Column.class);
                    if (("".equals(column.value()) && tempField.getName().equals(columnName)) || columnName.equals(column.value())) {
                        field = tempField;
                    }
                }
            }
            return field;
        }
        return null;
    }
    
    
    /**
     * 执行添加，并返回生成的主键 id
     *
     * @param obj 有数据的对象
     * @return 生成的主键；-1 操作失败
     */
    public static int insertReturnId(Object obj) {
        Map<String, Object> map = generateInsertSql(obj);
        if (map != null) {
            String sql = (String) map.get("sql");
            Object[] params = (Object[]) map.get("params");
            return insertReturnId(sql, params);
        }
        return -1;
    }
    
    /**
     * 执行添加：
     * 1. 需要将对象反射拼接成一个 SQL 语句 => INSERT INTO 表名 ( 字段名, ...) VALUES ( 值, ... )
     * 2. 获取属性中的值，作为参数
     * 3. 返回生成的主键 id
     *
     * @param sql    添加的 SQL 语句
     * @param params 参数
     * @return id
     */
    public static int insertReturnId(String sql, Object... params) {
        Connection conn = getCurrentConnection();
        int id = -1;
        
        if (conn != null) {
            PreparedStatement pst;
            ResultSet rs;
            try {
                pst = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
                for (int i = 1; i <= params.length; i++) {
                    pst.setObject(i, params[i - 1]);
                }
                pst.executeUpdate();
                rs = pst.getGeneratedKeys();
                rs.next();
                id = rs.getInt(1);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        
        return id;
    }
    
    
    /**
     * 根据对象生成添加的 SQL 语句和参数
     *
     * @param obj 生成添加的 SQL 语句和参数的对象
     * @return Map（添加了 SQL 语句和参数）；null 操作失败
     */
    private static Map<String, Object> generateInsertSql(Object obj) {
        Class<?> clazz = obj.getClass();
        Map<String, Object> map = null;
        
        if (clazz != null) {
            if (clazz.isAnnotationPresent(Table.class)) {
                StringBuilder beforeSql = new StringBuilder();
                StringBuilder afterSql = new StringBuilder();
                List<Object> params = new ArrayList<>();
                
                beforeSql.append("INSERT INTO ");
                afterSql.append(" ) VALUES ( ");
                
                Table table = clazz.getAnnotation(Table.class);
                String tableName = table.value();
                if (!StringUtil.isNotNull(tableName)) {
                    tableName = clazz.getSimpleName();
                }
                
                beforeSql.append(tableName);
                beforeSql.append(" ( ");
                
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true);
                    Object value;
                    try {
                        value = field.get(obj);
                    } catch (IllegalAccessException e) {
                        throw new RuntimeException(e);
                    }
                    
                    if (value != null) {
                        if (field.isAnnotationPresent(Id.class)) {
                            Id id = field.getAnnotation(Id.class);
                            String idName = id.value();
                            if (!StringUtil.isNotNull(idName)) {
                                idName = field.getName();
                            }
                            
                            beforeSql.append(idName);
                            beforeSql.append(", ");
                            afterSql.append("?, ");
                            params.add(value);
                        }
                        
                        if (field.isAnnotationPresent(Column.class)) {
                            Column column = field.getAnnotation(Column.class);
                            String fieldName = column.value();
                            if (!StringUtil.isNotNull(fieldName)) {
                                fieldName = field.getName();
                            }
                            beforeSql.append(fieldName);
                            beforeSql.append(", ");
                            afterSql.append("?, ");
                            params.add(value);
                        }
                        
                        if (field.isAnnotationPresent(ManyToOne.class)) {
                            ManyToOne manyToOne = field.getAnnotation(ManyToOne.class);
                            String fieldName = manyToOne.value();
                            if (!StringUtil.isNotNull(fieldName)) {
                                fieldName = getPrimaryKeyName(field.getType());
                            }
                            beforeSql.append(fieldName);
                            beforeSql.append(", ");
                            afterSql.append("?, ");
                            params.add(getPrimaryKeyValue(value));
                        }
                    }
                }
                String beforeString = beforeSql.substring(0, beforeSql.length() - 2);
                String afterString = afterSql.substring(0, afterSql.length() - 2) + " )";
                
                String sql = beforeString + afterString;
                map = new HashMap<>();
                map.put("sql", sql);
                map.put("params", params.toArray());
            }
        }
        
        return map;
    }
    
}
